Analysis of Housing Rental Market¶
Impact of Unemployment and Median Income on Rental Rates¶
This study is a review of the Rental Market and what, if any impact Unemployment Rates and Median Income have on pricing. The data curated for this study spans from 2013 to 2018. Additional Observations have been provided for further context.
Curated Data Sets¶
In [81]:
#Describe Dataset here
filename = 'data\zillow\MortgageRateConventionalFixed.csv'
mortgage_rates_src = pd.read_csv(filename)
#Describe Dataset here
filename = 'data\zillow\Affordability_Income_2018Q4.csv'
affordability_income_src = pd.read_csv(filename,encoding='latin-1',skipinitialspace=True)
#Median Rental Price - Single Family Residence
filename = 'data\zillow\City_MedianRentalPrice_Sfr.csv'
median_rental_price_sfr_src = pd.read_csv(filename,skipinitialspace=True)
#Median Rental Price Per Square Foot - Single Family Residence
filename = 'data\zillow\City_MedianRentalPricePerSqft_Sfr.csv'
median_rental_price_sfr_sqft_src = pd.read_csv(filename,skipinitialspace=True)
#Unemployment Rate - 20 years
filename = 'data/unemployment_rate_20_years.csv'
unemployment_rate_src = pd.read_csv(filename,encoding='latin-1',skipinitialspace=True)
#Monthly Listing all homes - city
filename = 'data/zillow/MonthlyListings_NSA_AllHomes_City.csv'
monthly_listings_allhomes_city_src = pd.read_csv(filename,encoding='latin-1',skipinitialspace=True)
#Sale Counts - city
filename = 'data/zillow/Sale_Counts_City.csv'
monthly_sale_counts_city_src = pd.read_csv(filename,encoding='latin-1',skipinitialspace=True)
#Sale prices - city
filename = 'data/zillow/Sale_Prices_City.csv'
monthly_sale_prices_city_src = pd.read_csv(filename,encoding='latin-1',skipinitialspace=True)
#Describe Dataset here
filename = 'data\zillow\Affordability_Wide_2019Q2_Public.csv'
affordability_index_src = pd.read_csv(filename,encoding='latin-1',skipinitialspace=True)
#Unemployment Rate - by state
filename = 'data/unemployment_rate_by_state.csv'
unemployment_rate_state_src = pd.read_csv(filename,encoding='latin-1',skipinitialspace=True)
# #Median Rental Price - Multi-Family Residence
# filename = 'data\zillow\City_MedianRentalPrice_Mfr5Plus.csv'
# median_rental_price_mfr_src = pd.read_csv(filename,skipinitialspace=True)
# #Median Rental Price Per Square Foot - Multi-Family Residence
# filename = 'data\zillow\City_MedianRentalPricePerSqft_Mfr5Plus.csv'
# median_rental_price_mfr_sqft_src = pd.read_csv(filename,skipinitialspace=True)
# #Rental Index Per Square Foot - All Homes
# filename = 'data\zillow\City_ZriPerSqft_AllHomes.csv'
# rental_index_all_sqft_src = pd.read_csv(filename,encoding='latin-1',skipinitialspace=True)
# #Median Value - All Homes
# filename = 'data\zillow\City_Zhvi_AllHomes.csv'
# median_value_all_src = pd.read_csv(filename,encoding='latin-1',skipinitialspace=True)
# #Median Value Per Square Foot - All Homes
# filename = 'data\zillow\City_MedianValuePerSqft_AllHomes.csv'
# median_value_all_sqft_src = pd.read_csv(filename,encoding='latin-1',skipinitialspace=True)
# #Count of Home Sales
# filename = 'data\zillow\Sale_Counts_City.csv'
# sales_count_src = pd.read_csv(filename,encoding='latin-1',skipinitialspace=True)
# #rental vacancy Rate - 10 years quarterly
# filename = 'data/rental_vacancy_rate_10_yrs.csv'
# rental_vacancy_rate_src = pd.read_csv(filename,encoding='latin-1',skipinitialspace=True)
#Variable for use in the notebook
housing_crash_date = ['2008','12']
state_geo = 'data/folium/us-states.json'
In [82]:
#hideme
median_rental_price_sfr_sqft = median_rental_price_sfr_sqft_src.copy()
median_rental_price_sfr_sqft.drop(columns=['State','RegionName','Metro','CountyName','SizeRank'],inplace=True)
median_rental_price_sfr_sqft.columns = pd.MultiIndex.from_tuples([tuple(c.split('-'))for c in median_rental_price_sfr_sqft.columns],names=['Year','Month'])
median_rental_price_sfr_sqft = pd.DataFrame(median_rental_price_sfr_sqft.median()).reset_index()
median_rental_price_sfr_sqft.columns=['Year','Month','RentalRateSqFt']
median_rental_price_sfr_sqft['Year'] = median_rental_price_sfr_sqft['Year'].astype(int)
median_rental_price_sfr_sqft['Month'] = median_rental_price_sfr_sqft['Month'].astype(int)
median_rental_price_sfr_sqft.head()
Out[82]:
In [83]:
median_rental_price_sfr = median_rental_price_sfr_src.copy()
median_rental_price_sfr.drop(columns=['State','RegionName','Metro','CountyName','SizeRank'],inplace=True)
median_rental_price_sfr.columns = pd.MultiIndex.from_tuples([tuple(c.split('-'))for c in median_rental_price_sfr.columns],names=['Year','Month'])
median_rental_price_sfr = pd.DataFrame(median_rental_price_sfr.median()).reset_index()
median_rental_price_sfr.columns=['Year','Month','RentalRate']
median_rental_price_sfr['Year'] = median_rental_price_sfr['Year'].astype(int)
median_rental_price_sfr['Month'] = median_rental_price_sfr['Month'].astype(int)
#median_rental_price_sfr.set_index('Year',inplace=True)
median_rental_price_sfr.head()
Out[83]:
In [84]:
mortgage_rates = mortgage_rates_src.copy()
mortgage_rates = mortgage_rates.drop(columns='TimePeriod')
mortgage_rates = mortgage_rates.rename(columns={'MortgageRateConventionalFixed':'MortgageRate'})
mortgage_rates['Date'] = pd.to_datetime(mortgage_rates['Date'], format='%Y-%m-%d')
mortgage_rates['Year'] = pd.DatetimeIndex(mortgage_rates['Date']).year
mortgage_rates['Month'] = pd.DatetimeIndex(mortgage_rates['Date']).month
mortgage_rates = mortgage_rates.drop(columns='Date')
mortgage_rates = mortgage_rates.groupby(['Year','Month']).median().reset_index()
mortgage_rates['Year'] = mortgage_rates['Year'].astype(int)
mortgage_rates['Month'] = mortgage_rates['Month'].astype(int)
#mortgage_rates.set_index('Year',inplace=True)
mortgage_rates.head()
Out[84]:
In [85]:
unemployment_rate = pd.DataFrame(unemployment_rate_src.copy())#.reset_index()
unemployment_rate = unemployment_rate.rename(columns={'LNS14000024':'UnemploymentRate'})
unemployment_rate['Date'] = pd.to_datetime(unemployment_rate['DATE'], format='%Y-%m-%d')
unemployment_rate['Year'] = pd.DatetimeIndex(unemployment_rate['Date']).year
unemployment_rate['Month'] = pd.DatetimeIndex(unemployment_rate['Date']).month
unemployment_rate = unemployment_rate.drop(columns='Date')
unemployment_rate = unemployment_rate.groupby(['Year','Month']).median().reset_index()
unemployment_rate['Year'] = unemployment_rate['Year'].astype(int)
unemployment_rate['Month'] = unemployment_rate['Month'].astype(int)
unemployment_rate.head()
Out[85]:
In [86]:
affordability_income = affordability_income_src.copy()
affordability_income['State'] = affordability_income["RegionName"].apply(lambda x: "".join(x.split(',')[1:]))
#affordability_income['City'] = affordability_income["RegionName"].apply(lambda x: "".join(x.split(',')[0]))
affordability_income.drop(columns=['RegionName','RegionID','SizeRank'],inplace=True)
affordability_income = affordability_income.set_index('State')
affordability_income.columns = pd.MultiIndex.from_tuples([tuple(c.split('-'))for c in affordability_income.columns],names=['Year','Month'])
#affordability_income = affordability_income.groupby('State').sum()#.median()
affordability_income = pd.DataFrame(affordability_income.median()).reset_index()
affordability_income.columns=['Year','Month','MedianIncome']
affordability_income['Year'] = affordability_income['Year'].astype(int)
affordability_income['Month'] = affordability_income['Month'].astype(int)
##median_rental_price_sfr.set_index('Year',inplace=True)
affordability_income.head()
Out[86]:
In [87]:
monthly_sale_counts_city = monthly_sale_counts_city_src.copy()
monthly_sale_counts_city.drop(columns=['RegionName','RegionID','SizeRank'],inplace=True)
monthly_sale_counts_city = monthly_sale_counts_city.set_index('StateName')
monthly_sale_counts_city.columns = pd.MultiIndex.from_tuples([tuple(c.split('-'))for c in monthly_sale_counts_city.columns],names=['Year','Month'])
#this dataset includes small cities so we're going to total by state before doing median
monthly_sale_counts_city = monthly_sale_counts_city.groupby('StateName').sum()#.median()
monthly_sale_counts_city = pd.DataFrame(monthly_sale_counts_city.median()).reset_index()
monthly_sale_counts_city.columns=['Year','Month','SaleCounts']
monthly_sale_counts_city['Year'] = monthly_sale_counts_city['Year'].astype(int)
monthly_sale_counts_city['Month'] = monthly_sale_counts_city['Month'].astype(int)
##median_rental_price_sfr.set_index('Year',inplace=True)
monthly_sale_counts_city.head()
Out[87]:
In [88]:
monthly_sale_prices_city = monthly_sale_prices_city_src.copy()
monthly_sale_prices_city.drop(columns=['StateName','RegionName','RegionID','SizeRank'],inplace=True)
monthly_sale_prices_city.columns = pd.MultiIndex.from_tuples([tuple(c.split('-'))for c in monthly_sale_prices_city.columns],names=['Year','Month'])
monthly_sale_prices_city = pd.DataFrame(monthly_sale_prices_city.median()).reset_index()
monthly_sale_prices_city.columns=['Year','Month','SalePrices']
monthly_sale_prices_city['Year'] = monthly_sale_prices_city['Year'].astype(int)
monthly_sale_prices_city['Month'] = monthly_sale_prices_city['Month'].astype(int)
#median_rental_price_sfr.set_index('Year',inplace=True)
monthly_sale_prices_city.head()
Out[88]:
In [89]:
monthly_listings_allhomes_city = monthly_listings_allhomes_city_src.copy()
monthly_listings_allhomes_city.drop(columns=['RegionName','RegionID','SizeRank'],inplace=True)
monthly_listings_allhomes_city = monthly_listings_allhomes_city.set_index('StateName')
monthly_listings_allhomes_city.columns = pd.MultiIndex.from_tuples([tuple(c.split('-'))for c in monthly_listings_allhomes_city.columns],names=['Year','Month'])
monthly_listings_allhomes_city = monthly_listings_allhomes_city.groupby('StateName').sum()
monthly_listings_allhomes_city = pd.DataFrame(monthly_listings_allhomes_city.median()).reset_index()
monthly_listings_allhomes_city.columns=['Year','Month','Listings']
monthly_listings_allhomes_city['Year'] = monthly_listings_allhomes_city['Year'].astype(int)
monthly_listings_allhomes_city['Month'] = monthly_listings_allhomes_city['Month'].astype(int)
#median_rental_price_sfr.set_index('Year',inplace=True)
monthly_listings_allhomes_city.head()
Out[89]:
In [90]:
#build metrics set from first candidate df
metrics_yearly = median_rental_price_sfr_sqft
metrics_yearly['Date'] = metrics_yearly['Month'].map(str)+ '-' +metrics_yearly['Year'].map(str)
metrics_yearly['Date'] = pd.to_datetime(metrics_yearly['Date'], format='%m-%Y')#.dt.strftime('%Y-Q%q')
metrics_yearly['Quarter'] = metrics_yearly['Date'].dt.quarter
#set our column order early so we don't have to deal with it later
metrics_yearly = metrics_yearly[['Date','Year','Month','Quarter','RentalRateSqFt']]
#merge in any other metrics we need
metrics_yearly = metrics_yearly.merge(unemployment_rate, left_on=['Year','Month'], right_on=['Year','Month'])
metrics_yearly = metrics_yearly.merge(median_rental_price_sfr, left_on=['Year','Month'], right_on=['Year','Month'])
metrics_yearly = metrics_yearly.merge(mortgage_rates,left_on=['Year','Month'], right_on=['Year','Month'])
metrics_yearly = metrics_yearly.merge(monthly_listings_allhomes_city,left_on=['Year','Month'], right_on=['Year','Month'])
metrics_yearly = metrics_yearly.merge(monthly_sale_counts_city,left_on=['Year','Month'], right_on=['Year','Month'])
metrics_yearly = metrics_yearly.merge(monthly_sale_prices_city,left_on=['Year','Month'], right_on=['Year','Month'])
metrics_yearly = metrics_yearly.merge(affordability_income,how='left',left_on=['Year','Month'], right_on=['Year','Month']).fillna(method='bfill')
metrics_yearly = metrics_yearly.dropna(how='any')
metrics_yearly.set_index('Date',inplace=True,drop=True)
#metrics_yearly['Quarter'] = metrics_yearly['Date']
#metrics_yearly = metrics_yearly.resample('Q').mean()
metrics_yearly.head()
Out[90]:
In [91]:
#hideme
metrics_yearly.describe()
Out[91]:
In [96]:
metrics_yearly_core = metrics_yearly.filter(items=['Year','Month','Quarter','RentalRateSqFt','UnemploymentRate','RentalRate','MedianIncome'])
metrics_yearly_core.head()
Out[96]:
In [97]:
#hideme
title = 'Scatter Matrix of Core Observations'
df = metrics_yearly_core.copy()#.drop(columns=['Month','Quarter'])
sns.pairplot(df, hue="Year", vars=['RentalRateSqFt','RentalRate','UnemploymentRate','MedianIncome'])
# Save the Figure
plt.savefig(f"figures\{title.replace(' ','_')}.png")
# Show the Figure
plt.show()
In [103]:
groups = metrics_yearly_anova_emp['UnemploymentRate'].unique()
groups
Out[103]:
In [104]:
stats.f_oneway(*[metrics_yearly_anova_emp[metrics_yearly_anova_emp["UnemploymentRate"] == grp]["RentalRateSqFt"] for grp in groups])
Out[104]:
In [106]:
groups = metrics_yearly_anova_inc['MedianIncome'].unique()
groups
Out[106]:
In [107]:
stats.f_oneway(*[metrics_yearly_anova_inc[metrics_yearly_anova_inc["MedianIncome"] == grp]["RentalRateSqFt"] for grp in groups])
Out[107]:
In [109]:
#build state name / id xref
with open(state_geo) as json_file:
state_geo_df = json.load(json_file)
state_geo_xref = json_normalize(state_geo_df['features']).filter(items=['id','properties.name'])
state_geo_xref = state_geo_xref.rename(columns={'id':'State','properties.name':'StateName'})#.set_index('State')
state_geo_xref.head()
Out[109]:
In [110]:
# create geodataframe and add centroids
gdf = gpd.GeoDataFrame.from_features(state_geo_df)
gdf['centroid_lon'] = gdf['geometry'].centroid.x
gdf['centroid_lat'] = gdf['geometry'].centroid.y
gdf.crs = {'init' :'epsg:4326'}
gdf.head()
Out[110]:
In [111]:
#merge w/ data points
median_rental_price_mfr_state_year = median_rental_price_sfr_src.copy()
median_rental_price_mfr_state_year = median_rental_price_mfr_state_year.merge(state_geo_xref, left_on='State', right_on='State')
median_rental_price_mfr_state_year.set_index(['RegionName','State','Metro','CountyName','SizeRank'],inplace=True)
median_rental_price_mfr_state_year.columns = pd.MultiIndex.from_tuples([tuple(c.split('-'))for c in median_rental_price_mfr_state_year.columns],names=['Year','Month'])
median_rental_price_mfr_state_year = median_rental_price_mfr_state_year.stack().reset_index().groupby(['State','StateName']).median().drop(columns='SizeRank')
gdf_income_tax = gdf.merge(median_rental_price_mfr_state_year.reset_index(),how='right', left_on='name', right_on='StateName')
gdf_income_tax = gdf_income_tax.fillna(method='bfill',axis=1).dropna(how='all',axis=1)
gdf_income_tax.head()
Out[111]:
MAP - Unemployment Rate (%)¶
In [112]:
#hideme
unemployment_rate_state = unemployment_rate_state_src.copy()
unemployment_rate_state.rename(columns={'State':'StateName'},inplace=True)
unemployment_rate_state = unemployment_rate_state.merge(state_geo_xref,left_on='StateName',right_on='StateName')
unemployment_rate_state.dropna(inplace=True)
years = [col for col in unemployment_rate_state.columns if col not in ['StateName','State']]
#render the map
map_name = 'Unemployment Rate (%)'
m = folium.Map([43, -100], zoom_start=4)
for year in years:
yr = folium.Choropleth(
geo_data=state_geo,
data=unemployment_rate_state,
columns=['State', year],
key_on='feature.id',
fill_color='YlGn',
fill_opacity=0.7,
line_opacity=0.2,
legend_name= f'{year} - {map_name}',
overlay=True,
name = f'{year}'
)
m.add_child(yr)
m.add_child(folium.LayerControl())
m.save(f"maps\{map_name}.html")
m
Out[112]:
MAP - Median Income ($)¶
In [113]:
#hideme
affordability_income_map_median = affordability_income_src.copy()
affordability_income_map_median['State'] = affordability_income_map_median["RegionName"].apply(lambda x: "".join(x.split(',')[1:]))
affordability_income_map_median.drop(columns=['RegionName','RegionID','SizeRank'],inplace=True)
affordability_income_map_median = affordability_income_map_median.set_index('State')
affordability_income_map_median.columns = pd.MultiIndex.from_tuples([tuple(c.split('-'))for c in affordability_income_map_median.columns],names=['Year','Month'])
affordability_income_map_median = affordability_income_map_median.stack()
affordability_income_map_median = affordability_income_map_median.groupby('State').median()
affordability_income_map_median = affordability_income_map_median.reset_index()
affordability_income_map_median['State'] = affordability_income_map_median['State'].str.strip()
affordability_income_map_median['State'] = affordability_income_map_median['State'].astype(str)
affordability_income_map_median = affordability_income_map_median.merge(state_geo_xref,left_on='State',right_on='State')
years = ['2010','2011','2012','2013','2014','2015','2016','2017','2018']
#render the map
map_name = 'Median Income ($)'
m = folium.Map([43, -100], zoom_start=4)
for year in years:
yr = folium.Choropleth(
geo_data=state_geo,
data=affordability_income_map_median,
columns=['State', year],
key_on='feature.id',
fill_color='YlGn',
fill_opacity=0.7,
line_opacity=0.2,
legend_name= f'{year} - {map_name}',
overlay=True,
name = f'{year}'
)
m.add_child(yr)
m.add_child(folium.LayerControl())
m.save(f"maps\{map_name}.html")
m
Out[113]:
MAP - Median Rent ($) w/ Overlay¶
In [114]:
#hideme
years = [col for col in gdf_income_tax.columns if col not in ['geometry','name','centroid_lon','centroid_lat','State','StateName']]
#render the map
map_name = 'Median Rent ($)'
m = folium.Map([43, -100], zoom_start=4)
for year in years:
yr = folium.Choropleth(
geo_data=state_geo,
data=gdf_income_tax,
columns=['State', year],
key_on='feature.id',
fill_color='YlGn',
fill_opacity=0.7,
line_opacity=0.2,
legend_name= f'{year} - {map_name}',
#overlay=True,
name = year
)
m.add_child(yr)
# add markers with basic information
fg = folium.FeatureGroup(name=f'{year}-Rent')
for lat, lon, val, name in zip(gdf_income_tax['centroid_lat'].tolist(), gdf_income_tax['centroid_lon'].tolist(), gdf_income_tax[year].tolist(), gdf_income_tax['name'].tolist()):
html = f"""
<h2>{name}<\h2><br>
<h4>Median Rent ({year}): {int(round(val,0))} HUF <\h4>
"""
fg.add_child(folium.Marker(location=[lat, lon], popup=html))
m.add_child(fg)
folium.LayerControl().add_to(m)
m.save(f"maps\{map_name}.html")
m
Out[114]: